<?xml version="1.0" encoding="iso-8859-1" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
		<title>FusionCharts v3 Documentation</title>
		<link rel="stylesheet" href="Style.css" type="text/css" />
	</head>
	<body>
		<table width="98%" border="0" cellspacing="0" cellpadding="3" align="center">
			<tr>
				<td><h2 class="pageHeader">Using FusionCharts with C# (ASP.NET) &gt; Plotting data from a 
						database
					</h2>
				</td>
			</tr>
			<tr>
				<td valign="top" class="text"><p>In this section, we'll show you how to use 
						FusionCharts and C# (ASP.NET) to plot charts from data contained in a database. 
						We'll create a pie chart to show &quot;Production by Factory&quot; using:
					</p>
					<ul>
						<li>
							<span class="codeInline">dataXML</span>
							method first.</li>
						<li>
							Thereafter, we'll convert this chart to use
							<span class="codeInline">dataURL</span>
							method.
						</li>
					</ul>
					<p>For the sake of ease, we'll use an Access Database. The database is present in
						<span class="codeInline">Download Package &gt; Code &gt; C# &gt; DB </span>folder. 
						You can, however, use any database with FusionCharts including MS SQL, Oracle, 
						mySQL etc.
					</p>
					<p><strong>Before you go further with this page, we recommend you to please see the 
							previous section &quot;Basic Examples&quot; as we start off from concepts 
							explained in that page. </strong>
					</p>
					<p class="highlightBlock">The code examples contained in this page are present in
						<span class="codeInline">Download Package &gt; Code &gt; C# &gt; DBExample </span>
						folder. The Access database is present in
						<span class="codeInline">Download Package &gt; Code &gt; C# &gt;</span>
						<span class="codeInline">DB</span>.
					</p>
				</td>
			</tr>
			<tr>
				<td valign="top" class="text">&nbsp;</td>
			</tr>
			<tr>
				<td valign="top" class="header">Database Structure
				</td>
			</tr>
			<tr>
				<td valign="top" class="text">Before we code the ASP.NET pages to retrieve data, 
					let's quickly have a look at the database structure.
				</td>
			</tr>
			<tr>
				<td valign="top" class="text"><img src="Images/Code_DB.gif" width="372" height="124" /></td>
			</tr>
			<tr>
				<td valign="top" class="text"><p>The database contains just 2 tables:</p>
					<ol>
						<li>
							<span class="codeInline">Factory_Master</span>: To store the name and id of 
							each factory</li>
						<li>
							<span class="codeInline">Factory_Output</span>: To store the number of units 
							produced by each factory for a given date.</li>
					</ol>
					<p>For demonstration, we've fed some dummy data in the database. Let's now shift 
						our attention to the ASP.NET page that will interact with the database, fetch 
						data and then render a chart.
					</p>
				</td>
			</tr>
			<tr>
				<td valign="top" class="text">&nbsp;</td>
			</tr>
			<tr>
				<td valign="top" class="header">Building the ASP.NET Page for dataXML Method
				</td>
			</tr>
			<tr>
				<td valign="top" class="text">The ASP.NET page for <span class="codeInline">dataXML</span> method example is named as <span class="codeInline">BasicDBExample.aspx</span> (in <span class="codeInline">DBExample</span> folder). It contains the following code (<span class="codeInline">GetFactorySummaryChartHtml()</span> method from code behind page also reproduced below): </td>
			</tr>
			<tr>
				<td valign="top" class="codeBlock"><p>
						&lt;%@ Page language="c#" Codebehind="BasicDBExample.aspx.cs" 
						AutoEventWireup="false" 
						Inherits="InfoSoftGlobal.GeneralPages.ASP.NET.DBExample.BasicDBExample" %&gt;<br>
						&lt;HTML&gt;<br>
						&nbsp;&lt;HEAD&gt;<br>
						&nbsp;&nbsp;&lt;TITLE&gt;FusionCharts - Database Example &lt;/TITLE&gt;<br>
						&nbsp;&nbsp;&lt;SCRIPT LANGUAGE="Javascript" 
						SRC="../../FusionCharts/FusionCharts.js"&gt;&lt;/SCRIPT&gt;
						<br>
						&nbsp;&lt;/HEAD&gt;<br>
						&nbsp;&lt;body&gt;<br>
						<strong>&nbsp;&nbsp;&nbsp;</strong>&lt;form id='form1' name='form1' method='post' runat=&quot;server&quot;&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;%=GetFactorySummaryChartHtml()%&gt; <br />
&nbsp;&nbsp;&nbsp;&lt;/form&gt;<br>
						&nbsp;&lt;/body&gt;<br>
						&lt;/HTML&gt;<br>
						<br>
						<strong><u>Code Behind page:</u></strong> <br />
						<br>
						public string <strong>GetFactorySummaryChartHtml</strong>()<br />
{<br />
&nbsp;&nbsp;<span class="codeComment">&nbsp;//In this example, we show how to connect FusionCharts to a database.<br />
&nbsp;&nbsp;&nbsp;//For the sake of ease, we've used an Access database which is present in<br />
&nbsp;&nbsp;&nbsp;//../DB/FactoryDB.mdb. It just contains two tables, which are linked to each<br />
&nbsp;&nbsp;&nbsp;//other. <br />
<br />
&nbsp;&nbsp;&nbsp;//xmlData will be used to store the entire XML document generated</span><br />
&nbsp;&nbsp;&nbsp;string xmlData;</p>
				  <p> &nbsp;&nbsp;&nbsp;<span class="codeComment">//Generate the chart element</span><br />
				    &nbsp;&nbsp;&nbsp;xmlData = &quot;&lt;chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units'&gt;&quot;;<br />
  <br />
				    &nbsp;&nbsp;<span class="codeComment">&nbsp;//Iterate through each factory</span><br />
				    &nbsp;&nbsp;&nbsp;string factoryQuery = &quot;select * from Factory_Master&quot;;<br />
				    &nbsp;&nbsp;&nbsp;using (OdbcConnection connection = DbHelper.Connection(DbHelper.ConnectionStringFactory))<br />
				    &nbsp;&nbsp;&nbsp;{<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;using (OdbcCommand factoryCommand = new OdbcCommand(factoryQuery, connection))<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;using (OdbcDataAdapter adapter = new OdbcDataAdapter(factoryCommand))<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DataTable table = new DataTable();<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;adapter.Fill ( table );</p>
				  <p> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;foreach ( DataRow row in table.Rows)<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string quantityQuery = &quot;select sum(Quantity) as TotOutput from Factory_Output where FactoryId=&quot; + row[&quot;FactoryId&quot;].ToString();<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;using (OdbcCommand quantityCommand = new OdbcCommand(quantityQuery,connection))<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>&nbsp;xmlData += &quot;&lt;set label='&quot; + row[&quot;FactoryName&quot;].ToString() + &quot;' value='&quot; + quantityCommand.ExecuteScalar().ToString() + &quot;' /&gt;&quot;;</strong><br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<br />
				    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<br />
				    &nbsp;&nbsp;&nbsp;connection.Close();</p>
				  <p> &nbsp;&nbsp;&nbsp;xmlData += &quot;&lt;/chart&gt;&quot;;<br />
				    &nbsp;&nbsp;&nbsp;}<br />
  <br />
				    &nbsp;&nbsp;<span class="codeComment">&nbsp;//Create the chart - Pie 3D Chart with data from xmlData</span><br />
				    &nbsp;&nbsp;<strong>&nbsp;return FusionCharts.RenderChart(&quot;../../FusionCharts/Pie3D.swf&quot;, &quot;&quot;, xmlData, &quot;FactorySum&quot;, &quot;600&quot;, &quot;300&quot;, false, false);</strong><br />
				    }</p>
				  <p><br>
			      </p></td>
			</tr>
			<tr>
				<td valign="top" class="text"><p>The following actions are taking place in this code:</p>
					<ol>
                      <li> We first include <span class="codeInline">FusionCharts.js</span> JavaScript class to enable easy embedding of FusionCharts and call <span class="codeInline">GetFactorySummaryChartHtml()</span> method contained in code behind page.</li>
					  <li>In code behind page <span class="codeInline">GetFactorySummaryChartHtml()</span> function, 
					    we then open a connection to Access database. </li>
					  <li> Thereafter, we generate the XML data document by iterating through the 
					    recordset and store it in <span class="codeInline">xmlData</span> variable. </li>
					  <li> Finally, we render the chart using <span class="codeInline">Functions.RenderChart()</span> method and pass <span class="codeInline">xmlData</span> as <span class="codeInline">dataXML</span>. </li>
				  </ol>
					<p>When you now run the code, you'll get an output as under: </p></td>
			</tr>
			<tr>
				<td valign="top" class="text"><img src="Images/Code_DBOut.jpg" width="572" height="273" class="imageBorder" /></td>
			</tr>
			<tr>
				<td valign="top" class="text">&nbsp;</td>
			</tr>
			<tr>
				<td valign="top" class="header">Converting the example to use dataURL method
				</td>
			</tr>
			<tr>
				<td valign="top" class="text"><p>Let's now convert this example to use dataURL method. 
						As previously explained, in dataURL mode, you need two pages:</p>
					<ol>
						<li>
							<strong>Chart Container Page</strong> - The page which embeds the HTML code to 
							render the chart. This page also tells the chart where to load the data from. 
							We'll name this page as
							<span class="codeInline">Default.aspx</span>.
						</li>
						<li>
							<strong>Data Provider Page</strong> - This page provides the XML data to the 
							chart. We'll name this page as
							<span class="codeInline">PieData.aspx</span></li>
					</ol>
					<p class="highlightBlock">The pages in this example are contained in<span class="codeInline"> Download Package &gt; Code &gt; C# &gt; DB_dataURL</span>
						folder.
					</p>
				</td>
			</tr>
			<tr>
				<td valign="top" class="text">&nbsp;</td>
			</tr>
			<tr>
				<td valign="top" class="header">Chart Container Page -
					<span class="codeInline">Default.aspx </span></td>
			</tr>
			<tr>
				<td valign="top" class="text"><span class="codeInline">Default.aspx</span> contains the following code (with code behind page) to render the chart: </td>
			</tr>
			<tr>
				<td valign="top" class="codeBlock">
					&lt;%@ Page language="c#" Codebehind="Default.aspx.cs" AutoEventWireup="false" 
					Inherits="InfoSoftGlobal.GeneralPages.ASP.NET.DB_dataURL._Default" %&gt;
					<br>
					&lt;HTML&gt;<br>
					&nbsp;&lt;HEAD&gt;<br>
					&nbsp;&nbsp;&lt;TITLE&gt;FusionCharts - dataURL and Database Example 
					&lt;/TITLE&gt;<br>
					&nbsp;&nbsp;&lt;SCRIPT LANGUAGE="Javascript" 
					SRC="../../FusionCharts/FusionCharts.js"&gt;&lt;/SCRIPT&gt;
					<br>
					&nbsp;&lt;/HEAD&gt;<br>
					&nbsp;&lt;body&gt;<br>
					<strong>&nbsp;&nbsp;&nbsp;&nbsp;</strong>&lt;form id='form1' name='form1' method='post' runat=&quot;server&quot;&gt; <br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>&lt;%=GetQuantityChartHtml()%&gt; </strong><br />
&nbsp;&nbsp;&nbsp;&lt;/form&gt;<br>
					&nbsp;&lt;/body&gt;<br>
					&lt;/HTML&gt;<br>
					<br>
					<strong><u>Code behind:</u></strong> <br>
					<strong>public string GetQuantityChartHtml()</strong><br />
{<br />
&nbsp;&nbsp;&nbsp;<span class="codeComment">//In this example, we show how to connect FusionCharts to a database <br />
&nbsp;&nbsp;&nbsp;//using dataURL method. In our other examples, we've used dataXML method<br />
&nbsp;&nbsp;&nbsp;//where the XML is generated in the same page as chart. Here, the XML data<br />
&nbsp;&nbsp;&nbsp;//for the chart would be generated in PieData.asp.<br />
<br />
&nbsp;&nbsp;&nbsp;//To illustrate how to pass additional data as querystring to dataURL, <br />
&nbsp;&nbsp;&nbsp;//we've added an animate	property, which will be passed to PieData.aspx. <br />
&nbsp;&nbsp;&nbsp;//PieData.aspx would handle this animate property and then generate the <br />
&nbsp;&nbsp;&nbsp;//XML accordingly.<br />
<br />
&nbsp;&nbsp;&nbsp;//For the sake of ease, we've used an Access database which is present in<br />
&nbsp;&nbsp;&nbsp;//../DB/FactoryDB.mdb. It just contains two tables, which are linked to each<br />
&nbsp;&nbsp;&nbsp;//other.<br />
<br />
&nbsp;&nbsp;&nbsp;//Variable to contain dataURL<br />
&nbsp;&nbsp;&nbsp;//Set DataURL with animation property to 1<br />
&nbsp;&nbsp;&nbsp;//NOTE: It's necessary to encode the dataURL if you've added parameters to it</span><br />
&nbsp;&nbsp;&nbsp;String dataURL = FusionCharts.EncodeDataURL(&quot;PieData.aspx?animate=1&quot;, false);<br />
<br />
&nbsp;&nbsp;&nbsp;<span class="codeComment">//Create the chart - Pie 3D Chart with dataURL as strDataURL</span><br />
&nbsp;&nbsp;&nbsp;<strong>return FusionCharts.RenderChart(&quot;../../FusionCharts/Pie3D.swf&quot;, dataURL, &quot;&quot;, &quot;FactorySum&quot;, &quot;600&quot;, &quot;300&quot;, false, false);</strong><br />
}					<br>				</td>
			</tr>
			<tr>
				<td valign="top" class="text"><p>In the above code, we're:</p>
					<ol>
                      <li> Including <span class="codeInline">FusionCharts.js</span> JavaScript class</li>
					  <li> Create the <span class="codeInline">dataURL</span> string and store it in <span class="codeInline">dataURL</span> variable. We append a dummy propery <span class="codeInline">animate</span> to show how to pass parameters to <span class="codeInline">dataURL</span>. After building the <span class="codeInline">dataURL</span>, we encode it using <span class="codeInline">EncodeDataURL</span> function defined in <span class="codeInline">FusionCharts class </span>. </li>
					  <li> Finally, we render the chart using <span class="codeInline">RenderChart()</span> method and set <span class="codeInline">dataURL</span> as <span class="codeInline">dataURL</span>. </li>
				  </ol></td>
			</tr>
			<tr>
				<td valign="top" class="header">Creating the data provider page
					<span class="codeInline">PieData.aspx </span></td>
			</tr>
			<tr>
				<td valign="top" class="text">PieData.aspx contains the following code to output XML 
					Data:
				</td>
			</tr>
			<tr>
				<td valign="top" class="codeBlock"><p>
						private void Page_Load(object sender, System.EventArgs e)<br>
		{<br>
			&nbsp;if (!IsPostBack)<br>
			&nbsp;{<br>
				&nbsp;&nbsp;<span class="codeComment">//This page generates the XML data for the Pie Chart contained in<br>
&nbsp;&nbsp;//Default.aspx. <br>
	<br>
&nbsp;&nbsp;//For the sake of ease, we've used an Access database which is present in<br>
&nbsp;&nbsp;//../DB/FactoryDB.mdb. It just contains two tables, which are linked to each<br>
&nbsp;&nbsp;//other. <br>
		<br>
&nbsp;&nbsp;//Database Objects - Initialization</span><br>
				&nbsp;&nbsp;string query;<br>
				&nbsp;&nbsp;<span class="codeComment">//xmlData will be used to store the entire XML document generated</span><br>
				&nbsp;&nbsp;string xmlData = String.Empty;<br>
				<br>
	<br>
				&nbsp;&nbsp;<span class="codeComment">//Default.aspx has passed us a property animate. We request that.</span><br>
				&nbsp;&nbsp;string animateChart;<br>
				&nbsp;&nbsp;animateChart = Request.QueryString["animate"];<br>
				&nbsp;<span class="codeComment">&nbsp;//Set default value of 1</span><br>
				&nbsp;&nbsp;if (animateChart != null && animateChart.Length == 0)<br>
				&nbsp;&nbsp;{<br>
					&nbsp;&nbsp;&nbsp;animateChart = "1";<br>
				&nbsp;&nbsp;}<br>
	<br>
	<span class="codeComment">&nbsp;&nbsp;//Generate the chart element</span><br>
				&nbsp;&nbsp;xmlData = &quot;&lt;chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' animation=' &quot; + animateChart + &quot;'&gt;&quot;;<br>
	<br>
				&nbsp;<span class="codeComment">&nbsp;//Iterate through each factory</span><br>
				&nbsp;&nbsp;query = "select * from Factory_Master";<br>
				&nbsp;&nbsp;using (OdbcConnection connection = FusionCharts.Connection(FusionCharts.ConnectionStringFactory))<br>
				&nbsp;&nbsp;{<br>
					&nbsp;&nbsp;&nbsp;OdbcCommand command = new OdbcCommand(query, connection);<br>
					&nbsp;&nbsp;&nbsp;DataTable factoryTable = new DataTable();<br>
				<br>
					&nbsp;&nbsp;&nbsp;using (OdbcDataAdapter factoryAdapter = new OdbcDataAdapter(command))<br>
					&nbsp;&nbsp;&nbsp;{<br>
					<br>
						&nbsp;&nbsp;&nbsp;&nbsp;factoryAdapter.Fill(factoryTable);<br>
					&nbsp;&nbsp;&nbsp;}<br>
<br>
					&nbsp;&nbsp;&nbsp;foreach(DataRow row in factoryTable.Rows)<br>
					&nbsp;&nbsp;&nbsp;{<br>
						&nbsp;&nbsp;&nbsp;&nbsp;string outputQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" + row["FactoryId"].ToString();<br>
						&nbsp;&nbsp;&nbsp;&nbsp;using (OdbcCommand outputCommand = new OdbcCommand(outputQuery, connection))<br>
						&nbsp;&nbsp;&nbsp;&nbsp;{<br>
							&nbsp;&nbsp;<strong>&nbsp;&nbsp;&nbsp;xmlData += &quot;&lt;set label='&quot; + row[&quot;FactoryName&quot;].ToString() + &quot;' value='&quot; + outputCommand.ExecuteScalar().ToString() + &quot;' /&gt;&quot;;</strong><br>
						&nbsp;&nbsp;&nbsp;&nbsp;}<br>
					&nbsp;&nbsp;&nbsp;}<br>
				&nbsp;&nbsp;}<br>
				&nbsp;&nbsp;<span class="codeComment">//Finally, close 
				<chart> element</chart>
				</span>
				<chart><br>
				&nbsp;&nbsp;xmlData += "&lt;/chart&gt;</chart>";<br>
		<br>
				&nbsp;&nbsp;<span class="codeComment">//Set Proper output content-type</span><br>
				&nbsp;&nbsp;Response.ContentType = "text/xml";<br>
	<br>
				&nbsp;<span class="codeComment">&nbsp;//Just write out the XML data<br>
&nbsp;&nbsp;//NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER</span><br>
				&nbsp;&nbsp;Response.Write( xmlData );<br>
			&nbsp;}<br>
		}<br>
					</p>
				</td>
			</tr>
			<tr>
				<td valign="top" class="text"><p>In the above page:</p>
					<ol>
                      <li> We first request the animate property which has been passed to it (from <span class="codeInline">dataURL</span>)</li>
					  <li> We generate the data and store it in <span class="codeInline">xmlData</span> variable</li>
					  <li> Finally, we write this data to output stream without any HTML tags. </li>
				  </ol>
					<p>When you view this page, you'll get the same output as before. </p></td>
			</tr>
		</table>
	</body>
</html>
